package com.ykt.model;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.ykt.common.constant.ParamNames;
import com.ykt.common.utils.Colls;
import com.ykt.common.utils.PageUtil;
import com.ykt.common.utils.UB;
import com.ykt.common.utils.page.PageData;
import com.ykt.common.utils.page.PageParam;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 订单Dao
 *
 * @author wing
 * @version 1.0.0
 */
public class Order extends BaseModel<Order> {
    private static final long serialVersionUID = 1L;

    public static final String order_no = "order_no";
    public static final String order_pno = "order_pno";
    public static final String order_type = "order_type";
    public static final String user_id = "user_id";
    public static final String receipt_name = "receipt_name";
    public static final String receipt_phone = "receipt_phone";
    public static final String shop_id = "shop_id";
    public static final String amount = "amount";
    public static final String pay_amount = "pay_amount";
    public static final String agent_fee = "agent_fee";
    public static final String pay_time = "pay_time";
    public static final String pay_serial_no = "pay_serial_no";
    public static final String status = "status";
    public static final String service_status = "service_status";
    public static final String address = "address";
    public static final String remarks = "remarks";
    public static final String delivery_time = "delivery_time";
    public static final String community_id = "community_id";
    public static final String community_name = "community_name";
    public static final String community_address = "community_address";
    public static final String community_contacts_name = "community_contacts_name";
    public static final String community_contacts_phone = "community_contacts_phone";

    public static final Order dao = new Order();

    // 订单状态
    public enum Status {
        MERCHANT_UNCONFIRMED, //用户已确认->待商户确认
        USER_UNPAYMENT, //商户已确认->待用户支付
        MERCHANT_UNDELIVERY, //用户已支付->待商户发货
        USER_UNRECEIPT, //商户已发货->待用户收货
        HAD_RECEIPT, //用户点击收货->已收货
        AFTER_SALING, //用户点击申请售后->售后中
        COMPLETED, //用户已收货->完成
        CANCEL, //取消
        MERCHANT_RETURN;// 已退款
    }

    // 售后状态
    public enum ServiceStatus {
        DELAY_SERVICE,// 待售后
        COMPLETED, //完成
    }

    // 状态类型
    public enum StatusType {
        STATUS,// 订单状态
        SERVICE_STATUS // 售后状态
    }

    // 订单类型
    public enum OrderType {
        /**单个订单*/
        SINGLE,
        /**父订单*/
        PARENT,
        /**子订单*/
        SON
    }

    @Override
    public Page<Order> pageFindModel(int pageNumber, int rows, Map<String, String> paramMap) {
        String select = "SELECT u.name userName,u.phone userPhone, s.name shopName, "
        		+ "o.* ";
        String sqlExceptSelect = " FROM `order` o left join `user` u on o.user_id = u.id left join `shop` s on o.shop_id=s.id ";

        String sqlOrderBy = " ORDER BY o.id DESC ";

        paramMap.put(OB, sqlOrderBy);

        return this.pageFindModel(select, sqlExceptSelect, pageNumber, rows, paramMap);
    }

    /**
     * 导出订单Excel数据查询
     *
     * @param paramMap
     * @return
     */
    public List<Order> findByOrderParam(Map<String, String> paramMap) {
        String select = "SELECT o.id, u.name userName,u.phone userPhone, s.name shopName,o.community_name, "
                + "o.amount,o.pay_amount payAmount, o.pay_time payTime, o.status, o.created_time createTime ";
        String sqlExceptSelect = " FROM `order` o LEFT JOIN `user` u ON o.user_id = u.id LEFT JOIN `shop` s ON o.shop_id=s.id ";
        String sqlOrderBy = " ORDER BY o.id DESC ";

        paramMap.put(OB, sqlOrderBy);

        Section section = this.toSection(paramMap);
        if (section != null) {
            sqlExceptSelect += section.getWhere();
            sqlExceptSelect += section.getOrderBy();
        }

        String sql = select + sqlExceptSelect;
        return this.find(sql, section.getValues().toArray());
    }

    /**
     * 导出Excel数据统计
     *
     * @param paramMap
     * @return
     */
    public Map<String, String> findByOrderParamCount(Map<String, String> paramMap) {
        String select = "SELECT count(*) countNum,SUM(IFNULL(o.amount,0)) countSum, SUM(IFNULL(o.pay_amount,0)) countPaySum ";
        String sqlExceptSelect = " FROM `order` o left join `user` u on o.user_id = u.id left join `shop` s on o.shop_id=s.id ";

        Section section = this.toSection(paramMap);
        if (section != null) {
            sqlExceptSelect += section.getWhere();
        }

        String sql = select + sqlExceptSelect;

        Map<String, String> map = new HashMap<String, String>();
        Order obj = findFirst(sql, section.getValues().toArray());
        map.put("countNum", obj.get("countNum").toString());
        map.put("countSum", obj.get("countSum").toString());
        map.put("countPaySum", obj.get("countPaySum").toString());

        return map;
    }

    public User getUser() {
        return User.dao.findById(getInt("user_id"));
    }


    /**
     * 采购清单报表sql
     */
    public List<Order> findByParam4Purchase(Map<String, String> paramMap) {
        String select = " SELECT  oi.goods_name, SUM(oi.goods_count*oi.goods_pcs) counts, CONVERT((oi.goods_price/oi.goods_pcs),DECIMAL(10,2)) unitPrice, SUM(oi.goods_amount) amountSum ";
        String sqlExceptSelect = " FROM `order` o LEFT JOIN `order_item` oi ON o.id=oi.order_id LEFT JOIN `user` u ON o.user_id = u.id LEFT JOIN `shop` s ON o.shop_id=s.id  ";

        String sqlOrderBy = " GROUP BY oi.goods_id,oi.goods_name";
        paramMap.put(OB, sqlOrderBy);

        Section section = this.toSection(paramMap);
        if (section != null) {
            sqlExceptSelect += section.getWhere();
            sqlExceptSelect += section.getOrderBy();
        }

        String sql = select + sqlExceptSelect;
        return this.find(sql, section.getValues().toArray());
    }

    /**
     * 分拣清单报表sql
     */
    public List<Order> findByParamForSorting(Map<String, String> paramMap) {
    	String select = "SELECT o.id orderId, o.receipt_name recName, s.name shopName, " +
                " o.receipt_phone recPhone, o.address recAddress, o.pay_amount payAmount, o.created_time createdTime," +
                " CAST(CONCAT(GROUP_CONCAT(oi.goods_name, oi.goods_count, oi.goods_unit), '') AS CHAR) orderDetails ";
        String sqlExceptSelect = " FROM `order` o LEFT JOIN `order_item` oi ON o.id=oi.order_id" +
                " LEFT JOIN `user` u ON o.user_id = u.id" +
                " LEFT JOIN `shop` s ON o.shop_id=s.id ";
        String sqlGroupBy = " GROUP BY o.id ";

        Section section = this.toSection(paramMap);
        if (section != null) {
            sqlExceptSelect += section.getWhere();
            sqlExceptSelect += section.getOrderBy();
        }
        String sql = select + sqlExceptSelect + sqlGroupBy;

        if (section == null || section.getValues() == null) {
            return this.find(sql);
        } else {
            return this.find(sql, section.getValues().toArray());
        }
    }
    
    /**
     * 财务统计商品报表sql
     */
    public List<Order> findByParamForProductCount(Map<String, String> paramMap) {
    	String select = "SELECT c.name categoryName, oi.goods_name, "
    			+ "g.sale_price, SUM(oi.goods_count) goodsCount, CONCAT(oi.goods_unit,'(', gu.summary, ')') goodUnit,"
    			+ " s.name shopName, o.community_name ";
        String sqlExceptSelect = " FROM `order` o LEFT JOIN `order_item` oi ON o.id=oi.order_id" +
        		" LEFT JOIN `goods` g ON oi.goods_id=g.id" +
        		" LEFT JOIN `goods_unit` gu ON g.unit_id=gu.id" +
        		" LEFT JOIN `category` c ON g.mg_cat_id=c.id" +
        		" LEFT JOIN `user` u ON o.user_id = u.id" +
                " LEFT JOIN `shop` s ON o.shop_id=s.id ";
        String sqlGroupBy = " GROUP BY g.id,o.community_name ";

        Section section = this.toSection(paramMap);
        if (section != null) {
            sqlExceptSelect += section.getWhere();
            sqlExceptSelect += section.getOrderBy();
        }
        String sql = select + sqlExceptSelect + sqlGroupBy;

        if (section == null || section.getValues() == null) {
            return this.find(sql);
        } else {
            return this.find(sql, section.getValues().toArray());
        }
    }
    
    
    /**
     * 财务统计订单商品明细sql
     */
    public List<Order> findByParamForOrderProductDetail(Map<String, String> paramMap) {
    	String select = "SELECT DATE_FORMAT(o.created_time,'%Y-%m-%d') orderDate,o.order_no, "
    			+ "(CASE WHEN o.status='MERCHANT_UNCONFIRMED' THEN '待商户确认' WHEN o.status='USER_UNPAYMENT' THEN '待用户支付' "
    			+ " WHEN o.status='MERCHANT_UNDELIVERY' THEN '待商户发货'  WHEN o.status='USER_UNRECEIPT' THEN '待用户收货' "
    			+ " WHEN o.status='COMPLETED' THEN '已完成' WHEN o.status='CANCEL' THEN '取消' "
    			+ " WHEN o.status='MERCHANT_RETURN' THEN '已退款' ELSE '' END) orderStatus, "
    			+ " s.name shopName, o.community_name,c.name categoryName, oi.goods_name, oi.goods_count goodsCount, CONCAT(oi.goods_unit,'(', gu.summary, ')') goodUnit, g.sale_price ";
        String sqlExceptSelect = " FROM `order` o " + 
        		" LEFT JOIN `order_item` oi ON o.id=oi.order_id" +
        		" LEFT JOIN `goods` g ON oi.goods_id=g.id" +
        		" LEFT JOIN `goods_unit` gu ON g.unit_id=gu.id" +
        		" LEFT JOIN `category` c ON g.mg_cat_id=c.id" +
        		" LEFT JOIN `user` u ON o.user_id = u.id" +
                " LEFT JOIN `shop` s ON o.shop_id=s.id ";
        
        String sqlGroupBy = " ";

        String sqlOrderBy = " ORDER BY o.id DESC ";
        paramMap.put(OB, sqlOrderBy);
        
        Section section = this.toSection(paramMap);
        if (section != null) {
            sqlExceptSelect += section.getWhere();
            sqlExceptSelect += section.getOrderBy();
        }
        String sql = select + sqlExceptSelect + sqlGroupBy;

        if (section == null || section.getValues() == null) {
            return this.find(sql);
        } else {
            return this.find(sql, section.getValues().toArray());
        }
    }

    /*******************************************CMS END**********************************************************/

    /**
     * 根据user Id 和 order id 查询父订单信息列表
     *
     * @param param 查询参数
     * @return
     */
    public PageData<Order> pageFindByUserIdAndStatus(PageParam<Map<String, Object>> param) {
        String select = "SELECT o.*, s.name shopName";
        String from = " FROM `order` o LEFT JOIN `shop` s ON o.shop_id=s.id";
        String where = " WHERE o.order_no=o.order_pno AND o.user_id=?";
        String sort = " ORDER BY o.id DESC ";

        int userId = UB.i(param.getP().get(Order.user_id), -1);
        String status = (String) param.getP().get(Order.status);

        List<Object> array = Colls.array2List((Object) userId);
        if (StrKit.notBlank(status)) {
            where += " AND o.status=?";
            array.add(status);
        }

        from += where + sort;
        Page<Order> page = this.paginate(param.getPageNo(), param.getPageSize(), select, from, array.toArray());

        return PageUtil.to(page);
    }

    /**
     * 根据user Id 和 order id 查询父订单信息列表
     *
     * @param param 查询参数
     * @return
     */
    public PageData<Order> pageFindByUserIdAndServiceStatus(PageParam<Map<String, Object>> param) {
        String select = "SELECT o.*, s.name shopName";
        String from = " FROM `order` o LEFT JOIN `shop` s ON o.shop_id=s.id";
        String where = " WHERE o.user_id=?";
        String sort = " ORDER BY o.id DESC ";

        int userId = UB.i(param.getP().get(Order.user_id), -1);
        List<Object> args = Colls.array2List((Object) userId);

        String serviceStatus = (String) param.getP().get(Order.service_status);
        if(StrKit.isBlank(serviceStatus)){
            where += " AND o.service_status IS NOT null";
        }
        if (StrKit.notBlank(serviceStatus)) {
            where += " AND o.service_status=?";
            args.add(serviceStatus);
        }

        from += where + sort;
        Page<Order> page = this.paginate(param.getPageNo(), param.getPageSize(), select, from, args.toArray());

        return PageUtil.to(page);
    }

    /**
     * 根据 订单ID 查询订单详情，不含商品信息
     *
     * @param orderId 订单ID
     * @return
     */
    public Order findById(Integer orderId) {
        String select = "SELECT o.*, s.name shopName, u.name userName";
        String sqlExceptSelect = " FROM `order` o" +
                " LEFT JOIN `shop` s ON o.shop_id=s.id" +
                " LEFT JOIN `user` u ON o.user_id=u.id" +
                " WHERE o.id=?";

        String sql = select + sqlExceptSelect;
        return this.findFirst(sql, orderId);
    }

    public List<Order> findByOrderPno(String orderPno) {
        String select = "SELECT o.*, s.name shopName";
        String from = " FROM `order` o LEFT JOIN `shop` s ON o.shop_id=s.id";
        String where = " WHERE o.order_no!=o.order_pno AND o.order_pno=?";

        return super.find(select + from + where, orderPno);
    }

    public int updateByOrderId(int orderId, String orderStatus) {
        String sql = "UPDATE `order` SET `status`=? WHERE order_pno IN (SELECT o.order_pno FROM ((SELECT order_pno FROM `order` WHERE id=?) o))";
        return Db.update(sql, orderStatus, orderId);
    }

    /**
     * 查询店铺的所有订单概要信息
     *
     * @param param 商铺ID
     * @return 订单概要信息
     */
    public PageData<Order> pageFindByShopId(PageParam<Integer> param) {
        String select = "SELECT o.amount, o.pay_amount, o.created_time, u.phone userName";
        String from = " FROM `order` o LEFT JOIN `user` u ON o.user_id=u.id";
        String where = " WHERE o.shop_id=?";
        String sort = " ORDER BY o.id DESC";

        from += where + sort;
        Page<Order> page = this.paginate(param.getPageNo(), param.getPageSize(), select, from, param.getP());

        return PageUtil.to(page);
    }

    /**
     * 查询店铺指定状态的订单信息
     *
     * @param param 商铺ID、订单状态
     * @return
     */
    public PageData<Order> pageFindByShopIdAndStatus(PageParam<Map<String, Object>> param) {
        String select = "SELECT o.id, u.phone userName, o.amount, o.pay_amount, o.agent_fee, o.status, o.service_status, o.created_time, o.pay_time";
        String from = " FROM `order` o LEFT JOIN `user` u ON o.user_id=u.id";
        String where = " WHERE o.shop_id=?";
        String sort = " ORDER BY o.id desc";

        int shopId = UB.i(param.getP().get(Order.shop_id), -1);
        String status = (String) param.getP().get(Order.status);
        String serviceStatus = (String) param.getP().get(Order.service_status);

        List<Object> params = Colls.array2List((Object) shopId);
        if (StrKit.notBlank(status)) {
            params.add(status);
            where += " and o.status=? ";
        }
        if (StrKit.notBlank(serviceStatus)) {
            params.add(serviceStatus);
            where += " and o.service_status=? ";
        }

        from += where + sort;
        Page<Order> page = this.paginate(param.getPageNo(), param.getPageSize(), select, from, params.toArray());

        return PageUtil.to(page);
    }

    public List<Order> findByMerchantUnconfirmed(int shopId, Date lastFindTime) {
        String sql = "SELECT o.id FROM `order` o WHERE o.shop_id=? AND o.created_time>?";
        return this.find(sql, shopId, lastFindTime);
    }

    public PageData<Order> pageFindByStatus(PageParam<String> param) {
        String select = "SELECT * ";
        String exceptSelect = " FROM `order` WHERE `status`=?"
                + " ORDER BY created_time ASC";

        Page<Order> page = this.paginate(param.getPageNo(), param.getPageSize(), select, exceptSelect, param.getP());
        return PageUtil.to(page);
    }

    /**
     * 查询店铺订单和详情
     */
    public PageData<Order> pageFindOrderAndItemByShopIdAndStatus(PageParam<Map<String, Object>> param) {
        if (param == null || param.getP() == null) {
            return null;
        }

        String select = "SELECT o.*, oi.*";
        String from = " FROM `order` o, order_item oi";
        String where = " WHERE o.`id`=oi.`order_id` " +
                " AND o.`shop_id`=?  AND o.`status` NOT IN('USER_UNPAYMENT','CANCEL') ";


        Map<String, Object> p = param.getP();
        List<Object> args = new ArrayList<>();

        int shopId = UB.i(p.get(ParamNames.shopId));
        args.add(shopId);

        String orderStatus =  (String)p.get(ParamNames.orderStatus);
        if(orderStatus != null){
            args.add(orderStatus);
            where += " AND o.`status`=?";
        }

        String orderDate =  (String)p.get(ParamNames.date);
        if(orderDate != null){
            args.add(orderDate);
            where += " AND DATE_FORMAT(o.`created_time`,'%Y-%m-%d')=?";
        }

        from = from + where;

        Page<Order> page = this.paginate(param.getPageNo(), param.getPageSize(), select, from, args.toArray());
        return PageUtil.to(page);
    }
}
